# -*- coding: utf-8 -*-
import scrapy
import pymysql

from scrapy.http import Request
from scrapy.selector import Selector
from soccerResult.items import SoccerResultItem
import re

class SoccerresultSpider(scrapy.Spider):
    name = 'soccerResult'
    allowed_domains = ['live.500.com']

    start_urls = []
    #读取请求的url列表
    file = open('urlList.txt','r',encoding='utf-8')
    urlList = file.readlines()
    for u in urlList:
        u = re.sub(r'\n','',u)
        start_urls.append(u)

    #'http://live.500.com/wanchang.php?e=2014-10-08'
    # start_urls = ['http://live.500.com/?e=2018-10-28']

    def parse(self, response):
        items= []
        sel= Selector(response)
        
#        big_urls=sel.xpath('//div[@id=\"tab01\"]/div/h3/a/@href').extract()#大类的url
#        big_titles=sel.xpath("//div[@id=\"tab01\"]/div/h3/a/text()").extract()
#        second_urls =sel.xpath('//div[@id=\"tab01\"]/div/ul/li/a/@href').extract()#小类的url
#        second_titles=sel.xpath('//div[@id=\"tab01\"]/div/ul/li/a/text()').extract()
        #提取最基本html信息

        baseInfo = sel.xpath('//tr[@status=\"4\"]').extract()
        baseInfo_text = sel.xpath('//tr[@status=\"4\"]/td[8]/text()').extract()
        baseInfo_fid = sel.xpath('//tr[@status=\"4\"]/@fid').extract()

        for i in range(0, len(baseInfo_fid)):

            tmp = baseInfo[i]
            halfGoal = Selector(text=baseInfo[i]).xpath('//tr/td[9]/text()').extract()[0]
            gameId = baseInfo_fid[i]
            homeTeam = Selector(text=baseInfo[i]).xpath('//tr/td[6]/a/text()').extract()[0]
            guestTeam = Selector(text=baseInfo[i]).xpath('//tr/td[8]/a/text()').extract()[0]
            fullGoal = Selector(text=baseInfo[i]).xpath('//tr/td[7]/div/a[1]/text()').extract()[0] +' - '+ Selector(text=baseInfo[i]).xpath('//tr/td[7]/div/a[3]/text()').extract()[0]
            finalResult =  Selector(text=baseInfo[i]).xpath('//tr/td[11]/text()').extract()[0]

            sql_insert ="insert into soccer_result \
                (gameId,homeTeam,guestTeam,halfGoal,fullGoal,finalResult) \
                values ('%s','%s','%s','%s','%s','%s')"
            tmpSql  = sql_insert % (gameId,homeTeam,guestTeam,
                                    halfGoal,fullGoal,finalResult)
            print("\ntmpSql",tmpSql)
            try:
                cur.execute(tmpSql)
                #提交
                db.commit()
            except Exception as e:
                #错误回滚
                db.rollback() 
        db.close() 
            
